import openpyxl
import cv2
import numpy as np
import matplotlib.pyplot as plt
from math import sqrt
from xml.dom.minidom import parse
import pytesseract
import time
import os

pytesseract.pytesseract.tesseract_cmd = r'C:\\Program Files\\Tesseract-OCR\\tesseract.exe'
tessdata_dir_config = '--tessdata-dir "c:\\Program Files\\Tesseract-OCR\\tessdata" --psm 7'


def write_worksheet(worksheet_read_path, worksheet_write_path, cells_address, binary_cell_path):
    """
    该函数的作用：将表格图象识别的内容写入对应的电子表格中
    :param binary_cell_path: 处理后的单元格图象
    :param cells_address: 每个单元格的
    :param worksheet_read_path: 电子表格模板存放的地址
    :param worksheet_write_path: 完成写入后的电子表格存放地址
    :return:
    """

    workbook = openpyxl.load_workbook(worksheet_read_path)
    worksheet = workbook.worksheets[0]
    col_dict = {0: 'A', 1: 'B', 2: 'C', 3: 'D', 4: 'E', 5: 'F', 6: 'G', 7: 'H', 8: 'I', 9: 'J', 10: 'K', 11: 'L', }
    #     hand_list = [5,26,27,28,29,30,31,32,33,34,36,37,38,39,40,41,42,43,44,56,57,58,59,60,61,62,63,64,
    #                  66,67,68,69,70,71,72,73,74,77,78,79,80,81,82,83,84,85,87,88,89,90,91,92,93,94,95,102,103]
    hand_list = [43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
                 69, 70, 71, 72, 73, 74, 75, 77, 79, 80, 81, 82, 83, 84, 85]
    hand_list = [242, 243, 244, 245, 24, 247, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 262, 263, 264,
                 265, 266, 267, 268, 269, 270, 271, 272, 273,
                 274, 275, 276, 277, 278, 279, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 294, 295,
                 296, 297, 298, 299, 300]
    img_read = cv2.imread
    image_to_string = pytesseract.image_to_string
    for i, cell in enumerate(cells_address):
        col_start = cell[0][0]
        # col_end = cell[0][1]
        row_start = cell[1][0]
        # row_end = cell[1][1]
        # print(row_start,row_end)
        index = col_dict[col_start] + str(row_start + 1)
        if i in hand_list:
            # print("原始数据：", worksheet[index].value)
            img = img_read(binary_cell_path + str(i) + ".jpg")
            text = image_to_string(img, lang='zimu4', config=tessdata_dir_config)
            text = text.replace(' ', '')
            worksheet[index].value = text[0:-2]
        else:
            # continue
            img = img_read(binary_cell_path + str(i) + ".jpg")
            text = image_to_string(img, lang='font3', config=tessdata_dir_config)
            text = text.replace(' ', '')
            worksheet[index].value = text[0:-2]
            print(text)

    if (workbook.save(filename=worksheet_write_path)):
        print("识别信息写入表格成功！")

    else:
        print("识别信息写入表格失败！")
